package jp.ac.u_tokyo.dch.dao.front;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.apache.commons.lang.xwork.StringUtils;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.GroupItems;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;
import jp.ac.u_tokyo.dch.view.Constants;

/**
* @version 1.1
*/
public class GroupItemsDao {

	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 * @param DBコネクションオブジェクト
	 */
	public GroupItemsDao(DBConnection dbconn){
		this.dbconn = dbconn;
	}

	/**
	 * グループ項目リストを取得する
	 * @param groupid グループID
	 * @return グループ項目リスト
	 * @throws DBException 例外
	 */
	public List<GroupItems> selectList(String groupid) throws DBException {

		List<GroupItems> list = new ArrayList<GroupItems>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT * FROM group_items WHERE groupid = ? and level = 1";

			ps = dbconn.prepareStatement(query);

			ps.setInt(1, Integer.valueOf(groupid));

			rs = ps.executeQuery();
			while (rs.next()){

				GroupItems groupItems = new GroupItems();
				String topfieldkey = rs.getString("topfieldkey");
				groupItems.setGroupId(rs.getString("groupid"));
				groupItems.setTopFieldKey(topfieldkey);
				groupItems.setDisplayName(rs.getString("displayname"));
				groupItems.setFieldKeyList(getFieldKeyList(topfieldkey, groupid));

				list.add(groupItems);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	private String getFieldKeyList(String topfieldkey, String groupid) throws DBException {

		StringBuilder sb = new StringBuilder();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT * FROM group_items WHERE topfieldkey = ? and groupid = ? and level > 1 order by level";

			ps = dbconn.prepareStatement(query);

			ps.setString(1, topfieldkey);
			ps.setInt(2, Integer.valueOf(groupid));

			rs = ps.executeQuery();
			while (rs.next()){
				sb.append(rs.getString("fieldkey"));
				sb.append(Constants.FIELDKEY_DEL);
			}

			if (sb.length() != 0) {

				sb.deleteCharAt(sb.length() - 1);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return sb.toString();

	}

	/**
	 * グループ項目を挿入する
	 * @param list グループ項目リスト
	 * @param groupid クループID
	 * @return 挿入件数情報
	 * @throws DBException 例外
	 */
	public void insertAll(Collection<GroupItems> list, int groupid)  throws DBException {

		for (GroupItems groupItem : list) {
			insertGroupItems(groupItem, groupid);
		}
	}

	private void insertGroupItems(GroupItems groupItems, int groupid) throws DBException {

		CustomPreparedStatement ps = null;
		try{
			String query = "INSERT INTO group_items (groupid,fieldkey,level,topfieldkey,displayname)  VALUES (?,?,?,?,?)";

			ps = dbconn.prepareStatement(query);
			String topFieldKey = groupItems.getTopFieldKey();
			String displayname = groupItems.getDisplayName();
			String fieldKeyList = groupItems.getFieldKeyList();
			int level = 1;

			int index = 1;
			ps.setInt(index++, groupid);
			ps.setString(index++, topFieldKey);
			ps.setInt(index++, level++);
			ps.setString(index++, topFieldKey);
			ps.setString(index++, displayname);

			ps.addBatch();

			if (!StringUtils.isEmpty(fieldKeyList)) {
				for (String field : fieldKeyList.split(Constants.FIELDKEY_DEL)) {

					index = 1;
					ps.setInt(index++, groupid);
					ps.setString(index++, field);
					ps.setInt(index++, level++);
					ps.setString(index++, topFieldKey);
					ps.setString(index++, null);

					ps.addBatch();
				}
			}

			ps.executeBatch();

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
	}


	/**
	 * グループ項目を削除する
	 * @param groupid クループID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int deleteAll(Integer groupid)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "DELETE FROM group_items WHERE groupid = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, groupid);
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}
}
